<?php
    class Admincp_Model_Article extends Zend_Db_Table_Abstract
    {
        protected $_db;
        
        public function __construct()
        {
            $this->_db = Zend_Registry::get('db');
        }
        
        public function listArticle($locale)
        {
            $sql = $this->_db->select()
                            ->from('article AS a')
                            ->join('article_language AS al', 'a.id = al.article_id', array('title'))
                            ->join('language AS l', 'al.language_id = l.id', null)
                            ->where('l.locale = ?', $locale)
                            ->order('a.id DESC');
            return $sql;
        }
        
        public function getArticleById($id)
        {
            $sql = $this->_db->select()
                            ->from('article')
                            ->where('id = ?', $id);
            $result = $this->_db->fetchRow($sql);
            return $result;
        }
        
        public function getArticleLanguageById($id)
        {
            $sql = $this->_db->select()
                            ->from('article_language AS al', array('title', 'description'))
                            ->join('language AS l', 'al.language_id = l.id', array('locale'))
                            ->where('al.article_id = ?', $id);
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function checkArticle($title, $id = null)
        {
            $sql = $this->_db->select()
                            ->from('article_language')
                            ->where('title = ?', $title);
            if($id != null) {
                $sql = $sql->where('article_id <> ?', $id);
            }
            $result = $this->_db->fetchAll($sql);
            return count($result);
        }
        
        public function addArticle($data)
        {
            $this->_db->insert('article',$data);
            return $this->_db->lastInsertId();
        }
        
        public function addArticleLanguage($data)
        {
            $this->_db->insert('article_language',$data);
        }
        
        public function updateArticle($data, $id)
        {
            $where = $this->getAdapter()->quoteInto('id = ?', $id);
            $this->_db->update('article', $data, $where);
        }
        
        public function updateArticleLanguage($data,$id)
        {
            $where = 'article_id = '.$id.' AND language_id = '.$data['language_id'];
            $this->_db->update('article_language', $data, $where);
        }
        
        public function getImageById($id)
        {
            $sql = $this->_db->select()
                            ->from('article', array('image'))
                            ->where('id = ?', $id);
            $result = $this->_db->fetchOne($sql);
            return $result;
        }
        
        public function deleteArticle($id)
        {
            $id = (int) $id;
		    $where = $this->getAdapter()->quoteInto('id = ?', $id);
            $image = $this->getImageById($id);
            if(is_file(PUBLIC_PATH . '/files/article/' . $image)) {
                @unlink(PUBLIC_PATH . '/files/article/' . $image);
            }
            $this->_db->delete('article', $where);
            $where = $this->getAdapter()->quoteInto('article_id = ?', $id);
            $this->_db->delete('article_language', $where);
        }
        
        public function deleteMultiArticle($listId)
        {
            foreach($listId as $id) {
                $this->deleteArticle($id);
            }
        }
        
        public function searchArticle($data, $locale)
        {
            $sql = $this->_db->select()
		                    ->from('article AS a')
                            ->join('article_language AS al', 'a.id = al.article_id', array('title'))
                            ->join('language AS l', 'al.language_id = l.id', null)
                            ->where('l.locale = ?', $locale);
		    if($data['title'] != '') {
		        $sql = $sql->where('al.title_plaintext LIKE ?', $data['title']);
		    }
		    if($data['status'] != 0) {
		        $sql = $sql->where('a.status = ?', $data['status']);
		    }
		    return $sql;
        }
    }
